--06-05-2008 - UPDATE INVOICE NUMBER AND DATE FOR PURCHASE ORDERS (table PurPOUserOrderHeader)
--TrackIt #8343 and #8365
--The invoice data was not transfered from Insight V1 in May 2008 and this script fixes the problem.
----------------------------------------------------------------------------------------------
--Step 1.
--add 2 new columns to tables PurPOUserOrderHeader and PurJournalUserOrderHeader
---------
--add 2 columns to table PurPOUserOrderHeader 05-29-2008
--InvoiceNumber
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurPOUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceNumber'  )) 
ALTER TABLE PurPOUserOrderHeader ADD InvoiceNumber varchar(16) NULL
--InvoiceDate
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurPOUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceDate' ))
ALTER TABLE PurPOUserOrderHeader ADD InvoiceDate datetime NULL

--add 2 columns to table PurJournalUserOrderHeader 05-29-2008
--InvoiceNumber
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurJournalUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceNumber'  )) 
ALTER TABLE PurJournalUserOrderHeader ADD InvoiceNumber varchar(16) NULL
--InvoiceDate
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurJournalUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceDate' ))
ALTER TABLE PurJournalUserOrderHeader ADD InvoiceDate datetime NULL

--Step 2.
--Creates tables poh and pod - Purchase Orders from V1

--Step 2a.
--Clear InvoiceNumbers
update PurPOUserOrderHeader set InvoiceNumber=null, InvoiceDate=null

--step 3. Backup table
--------------------
-- check if backup table exists
IF OBJECT_ID('PurPOUserOrderHeaderNOINVOICE') IS NOT NULL 
				DROP TABLE PurPOUserOrderHeaderNOINVOICE

select * into PurPOUserOrderHeaderNOINVOICE
from PurPOUserOrderHeader

--------------------
--Step 4.
--Create table pohA - with included TotalAmount
--------------------
IF OBJECT_ID('pohA') IS not NULL drop table pohA 
select *
into pohA
from
(
select poh.nUserOrderId,poh.sInvoiceNumber,poh.dtInvoiceDate, poh.sPSPOID, poh.sVendorName, poh.sPSReqID, sum(cAmount) as OrderAmount, count(1) as counter from poh
inner join pod on poh.nUserOrderId = pod.nUserOrderId
group by poh.nUserOrderId, poh.sInvoiceNumber,poh.dtInvoiceDate, poh.sPSPOID, poh.sVendorName, poh.sPSReqID
) pox
--------------------

--Step 5.
--Update PurPOUserOrderHeader InvoiceNumber and InvoiceDate
--------------------
declare
		@uId int
		,@inv varchar(25)
		,@invD datetime
		,@PurUserOrderHeaderId	int
		,@TotalAmount money
		,@POId varchar(10)
		,@VendorName varchar(64)
		,@PSRequisitionNumber varchar(50)
		,@InvoiceNumber varchar(16)
		,@rec int
		,@cnt int
		,@nUserOrderId int
		,@sInvoiceNumber varchar(25)
		,@dtInvoiceDate datetime
		,@sPSPOID varchar(10)
		,@sVendorName varchar(25)
		,@sPSReqID varchar(10)
		,@OrderAmount money

select @rec = 0
		,@cnt = 0

	  DECLARE purcursor CURSOR FOR
	  SELECT 
		nUserOrderId
		,sInvoiceNumber
		,dtInvoiceDate
		,sPSPOID
		,sVendorName
		,sPSReqID
		,OrderAmount
	    from pohA

	  OPEN purcursor
	  FETCH NEXT FROM purcursor
		into @nUserOrderId
		,@sInvoiceNumber
		,@dtInvoiceDate
		,@sPSPOID
		,@sVendorName
		,@sPSReqID
		,@OrderAmount

	  WHILE (@@FETCH_STATUS = 0)
	  BEGIN

		select top 1 @uId = PurUserOrderHeaderId
		from PurPOUserOrderHeader
		where
			TotalAmount = @OrderAmount
			and POId = @sPSPOID
			and VendorName = @sVendorName
			and PSRequisitionNumber = @sPSReqID
			and InvoiceNumber is null
			
			
			if @uId is not null
			begin
		select @cnt = @cnt + 1
print @cnt
print @uId
				update PurPOUserOrderHeader
					set InvoiceNumber = substring(@sInvoiceNumber,1,16)
						,InvoiceDate = @dtInvoiceDate
				where PurUserOrderHeaderId = @uId
			end

	  FETCH NEXT FROM purcursor
		into @nUserOrderId
		,@sInvoiceNumber
		,@dtInvoiceDate
		,@sPSPOID
		,@sVendorName
		,@sPSReqID
		,@OrderAmount

	  END --while

	  CLOSE purcursor
	  DEALLOCATE purcursor
------------------------------

--select * from PurPOUserOrderHeader
--select * from pohA
